﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace myAC.DataProviders
{
    #region old
    //    public class MSSolveSRProvider : IDataProvider<SR>
//    {

//        /// <summary>
//        /// Get SRs for a specific topicFullpath
//        /// </summary>
//        /// <param name="rootCallCodes"></param>
//        /// <returns></returns>
//        public List<SR> GetData(String rootCallCodes)
//        {
//            List<SR> srs = new List<SR>();
//            SqlConnection conn = new SqlConnection();
//            conn.ConnectionString = @"Data Source=CO1MSSDWLDB12;Initial Catalog=WorkDB;Integrated Security=True";//@"Data Source=PQOCSTSDB01.redmond.corp.microsoft.com;Initial Catalog=AzureSR;Integrated Security=True";

//            SqlCommand cmd = conn.CreateCommand();
//            String cmdText = @"select fsr.ServiceRequestNumber, st.SupportTopicFullPath, st.CallCodeID, st.SupportTopicDerivedID, st.IsActive, sr.Ngim_ExternalTitle, sr.Ngim_CustomerReportedSymptoms
//	    from [FAREAST\wenjinxu].vwFactServiceRequest fsr  with (nolock)
//		inner join [FAREAST\wenjinxu].vwDimSupportTopic st  with (nolock) on fsr.CurrentSupportTopicDerivedID = st.SupportTopicDerivedID
//		left join [FAREAST\mingshao].Ngim_Servicerequest sr  with (nolock) on fsr.ServiceRequestNumber=sr.Ngim_sr_number -- we need to use left join here, because sr may not exist in MSSolve/Ngim
//		left join [FAREAST\wenjinxu].vwDimServiceRequestAttribute att with (nolock) on fsr.SRAttributesDerivedID=att.ServiceRequestAttributesDerivedID
//		where st.SupportTopicLevel1ID in ({0})
//		and fsr.SupportLanguageDerivedID in (6,7)  --en-GB  en-us
//		and att.SRType='Incident'
//		and att.SRStatus='Closed'
//        and fsr.CreatedTime > '2013-12-1'
//        and fsr.CurrentSupportTopicDerivedID <> -1 --according to business, CurrentSupportTopicDerivedID = -1 may be created by phone. We can leave it.
//        and (sr.Ngim_ExternalTitle is not null and sr.Ngim_CustomerReportedSymptoms is not null) -- there're many SRs with no Title nor body!!!
//        ";

//            #region wenjin's sql
////            String cmdTextWenjin = @"		
////		select 
////fsr.ServiceRequestNumber,
////sr.Ngim_ExternalTitle,
////sr.Ngim_InternalTitle,
////sr.Ngim_CustomerReportedSymptoms,
////fsr.servicerequestnumber, 
////t.SupportTopicDerivedID,
////t.CallCodeID,
////--fsr.CurrentSupportTopicDerivedID categoryId
////t.supporttopiclevel2id categoryId,
////dst.SupportTopicFullPath, fsr.CreatedTime
////,fsr.InitialSupportTopicDerivedID
////,(select top 1 SupportTopicFullPath from vwDimSupportTopic where SupportTopicDerivedID=fsr.InitialSupportTopicDerivedID)
////
////from dbo.vwFactServiceRequest fsr with(nolock)
////inner join dbo.vwDimSupportTopic dst with(nolock) on fsr.CurrentSupportTopicDerivedID=dst.SupportTopicDerivedID
////inner join dbo.vwDimServiceRequestAttribute  a with(nolock)  on fsr.SRAttributesDerivedID=a.ServiceRequestAttributesDerivedID
////inner join vwDimSupportTopic t   with(nolock)  on fsr.CurrentSupportTopicDerivedID=t.SupportTopicDerivedID
////left join dbo.Ngim_Servicerequest sr with (nolock) on sr.Ngim_sr_number=fsr.ServiceRequestNumber
////
////where fsr.SupportLanguageDerivedID in (6,7)  -- en-gb en-us
////and fsr.CreatedTime>='2013-12-01'
////and a.SRType='Incident'
////and a.SRStatus='Closed'
////and sr.createdbyyominame='MSSolve Integration Account Account'
////and sr.Ngim_CustomerReportedSymptoms not like 'SR created by:%'
////and t.SupportTopicFullPath like '{0}\%'
////and sr.Ngim_CustomerReportedSymptoms not like 'Q:%'
////and t.isactive=1";
//            #endregion

//            cmd.CommandText = String.Format(cmdText, rootCallCodes);
//            conn.Open();
//            SqlDataReader rdr = cmd.ExecuteReader();
//            while(rdr.Read())
//            {
//                SR sr = new SR();
//                SupportTopic topic = new SupportTopic();
//                sr.Topic = topic;

//                sr.SrNumber = rdr["ServiceRequestNumber"].ToString();
//                sr.RawExternalTitle = rdr["Ngim_ExternalTitle"].ToString();
//                sr.RawBody = rdr["Ngim_CustomerReportedSymptoms"].ToString();


//                if(sr.RawExternalTitle.Length==0 || sr.RawBody.Length==0)
//                {
//                    Console.WriteLine("empty!");
//                }

//                topic.SupportTopicFullpath = rdr["SupportTopicFullPath"].ToString();
//                topic.SupportTopicDerivedId = rdr["SupportTopicDerivedId"].ToString();
//                topic.CallCodeID = rdr["CallCodeID"].ToString();
//                topic.IsActive = String.Compare(rdr["IsActive"].ToString(), "1") == 0;

//                srs.Add(sr);
//                Console.Write("\rprocessed: {0,6}", srs.Count);

//                ACInstance instance = new ACInstance();
//                instance.TextFeatures["ServiceRequestNumber"] = rdr["ServiceRequestNumber"].ToString();
//                instance.TextFeatures["Ngim_ExternalTitle"] = rdr["Ngim_ExternalTitle"].ToString();
//                instance.TextFeatures["Ngim_CustomerReportedSymptoms"] = rdr["Ngim_CustomerReportedSymptoms"].ToString();
//                instance.OtherFeatures["Topic"] = topic;

//            }

//            return srs;
//        }
    //    }
    #endregion


    public class MSSolveSRProvider2 : IDataProvider<ACInstance>
    {

        /// <summary>
        /// Get SRs for a specific topicFullpath
        /// </summary>
        /// <param name="rootCallCodes"></param>
        /// <returns></returns>
        public ACInstance[] GetData(String rootCallCodes)
        {
            List<ACInstance> instances = new List<ACInstance>();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=CO1MSSDWLDB12;Initial Catalog=WorkDB;Integrated Security=True";//@"Data Source=PQOCSTSDB01.redmond.corp.microsoft.com;Initial Catalog=AzureSR;Integrated Security=True";

            SqlCommand cmd = conn.CreateCommand();
            String cmdText = @"select fsr.ServiceRequestNumber, fsr.CreatedTime, st.SupportTopicFullPath, st.CallCodeID, st.SupportTopicDerivedID, st.IsActive, sr.Ngim_ExternalTitle, sr.Ngim_CustomerReportedSymptoms
	    from [FAREAST\wenjinxu].vwFactServiceRequest fsr  with (nolock)
		inner join [FAREAST\wenjinxu].vwDimSupportTopic st  with (nolock) on fsr.CurrentSupportTopicDerivedID = st.SupportTopicDerivedID
		left join [FAREAST\mingshao].Ngim_Servicerequest sr  with (nolock) on fsr.ServiceRequestNumber=sr.Ngim_sr_number -- we need to use left join here, because sr may not exist in MSSolve/Ngim
		left join [FAREAST\wenjinxu].vwDimServiceRequestAttribute att with (nolock) on fsr.SRAttributesDerivedID=att.ServiceRequestAttributesDerivedID
		where st.SupportTopicLevel1ID in ({0})
		and fsr.SupportLanguageDerivedID in (6,7)  --en-GB  en-us
		and att.SRType='Incident'
		and att.SRStatus='Closed'
        and fsr.CreatedTime > '2013-10-1'
        and fsr.CreatedTime < '2013-12-1'
        and fsr.CurrentSupportTopicDerivedID <> -1 --according to business, CurrentSupportTopicDerivedID = -1 may be created by phone. We can leave it.
        and (sr.Ngim_ExternalTitle is not null and sr.Ngim_CustomerReportedSymptoms is not null) -- there're many SRs with no Title nor body!!!
        ";

            cmd.CommandText = String.Format(cmdText, rootCallCodes);
            try
            {
                Console.WriteLine("DB connection timeout value: {0}s.", conn.ConnectionTimeout);
                conn.Open();
                cmd.CommandTimeout = 12000; // sometimes it took really long to get the result back. I don't have time to optimize it yet.
                Console.WriteLine("Command timeout value: {0}s.", cmd.CommandTimeout);

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    SupportTopic topic = new SupportTopic();

                    topic.SupportTopicFullpath = rdr["SupportTopicFullPath"].ToString();
                    topic.SupportTopicDerivedId = rdr["SupportTopicDerivedId"].ToString();
                    topic.CallCodeID = rdr["CallCodeID"].ToString();
                    topic.IsActive = String.Compare(rdr["IsActive"].ToString(), "1") == 0;

                    ACInstance instance = new ACInstance();
                    instance.InstanceName = rdr["ServiceRequestNumber"].ToString();


                    instance.TextFeatures["Ngim_ExternalTitle"] = rdr["Ngim_ExternalTitle"].ToString();
                    instance.TextFeatures["Ngim_CustomerReportedSymptoms"] = rdr["Ngim_CustomerReportedSymptoms"].ToString();

                    instance.OtherFeatures["Topic"] = topic;
                    instance.OtherFeatures["CreatedTime"] = rdr["CreatedTime"].ToString();

                    instances.Add(instance);
                    Console.Write("\rObtained: {0,6}", instances.Count);
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return instances.ToArray();
        }
    }

}
